package com.meng.sql.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class HSQLDBTest {

	public static void main(String[] args) throws Exception {
		Connection conn = null;
		long start, end;
		try {
			// hsqldb
//			Class.forName("org.hsqldb.jdbc.JDBCDriver");

//			String url = "jdbc:hsqldb:hsql://localhost:9001/meng";
//			String table = "test_server";

//			String url = "jdbc:hsqldb:file:c:/sqlite3/my3";
//			String table = "test_file_memory";
//			String table = "test_file_cached";

//			String url = "jdbc:hsqldb:mem:memdb";
//			String table = "test_mem";

			// h2
			Class.forName("org.h2.Driver");
//			String url = "jdbc:h2:tcp://localhost:9092/c:/sqlite3/h2.db";
//			String url = "jdbc:h2:file:c:/sqlite3/h2.db";
			String url = "jdbc:h2:mem:memdb";
			String table = "test_mem";

			// sqlite
//			Class.forName("org.sqlite.JDBC");
//			String url = "jdbc:sqlite:c:/sqlite3/meng.db";
//			String url = "jdbc:sqlite::memory:";
//			String url = "jdbc:sqlite:";
//			String table = "test_file";

//			long start = System.currentTimeMillis();
//			conn = DriverManager.getConnection(url, "sa", "");
			conn = DriverManager.getConnection(url);
//			long end = System.currentTimeMillis();

			System.out.println(conn);


			Statement stmt = conn.createStatement();
//			ResultSet rs = stmt.executeQuery("select count(*) from " + table);
//			if (rs.next())
//				System.out.println(rs.getInt(1));
//			rs.close();

			stmt.executeUpdate("drop table if exists " + table);
			stmt.executeUpdate("create table " + table + " (id int, name varchar(20))");
//			stmt.executeUpdate("create cached table " + table + " (id integer, name varchar(20))");
//			stmt.executeUpdate("create memory table " + table + " (id integer, name varchar(20))");


			PreparedStatement ps = conn.prepareStatement("insert into " + table + " (id, name) values(?, ?)");
//

			start = System.currentTimeMillis();
			stmt.execute("begin");
			for (int i = 0; i < 1000000; i++) {
				ps.setInt(1, i);
				ps.setString(2, "meng_" + i);
				ps.executeUpdate();
			}
			stmt.execute("commit");
			end = System.currentTimeMillis();
			System.out.println("用时：" + (end - start) + "毫秒");
//
			ps.close();

//			stmt.execute("shutdown");
//			start = System.currentTimeMillis();
//			stmt.executeUpdate("restore from c:/sqlite3/mem.db");
//			end = System.currentTimeMillis();
//			System.out.println("读取用时：" + (end - start) + "毫秒");

//			start = System.currentTimeMillis();
//			stmt.executeUpdate("insert into " + table + " (id, name) values(100000, 'meng_xxx')");
//			stmt.executeUpdate("backup to c:/sqlite3/mem2.db");
//			end = System.currentTimeMillis();
//			System.out.println("备份用时：" + (end - start) + "毫秒");

			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
			conn.close();
		}
	}
}
